﻿Imports Microsoft.Office.Interop.Excel
Imports System.IO
Imports System.Text.RegularExpressions
Public Class InputParser
    Implements IDisposable
    Private rules As Dictionary(Of String, Dictionary(Of Object, Integer))
    Private Xl As Application
    Private iWb As Workbook, oWB As Workbook
    Private iWs As Worksheet, oWs As Worksheet
    Private _date As Date
    Private _dateSet As Boolean
    Private cachepath As String
    Public Property err_global As List(Of String)
    Public Property succs_global_fromcodes As List(Of SuccObject)
    Private coeffs As SortedList(Of String, CoeffObject)
    'Dim matching As Boolean
    Sub Dispose() Implements IDisposable.Dispose
        Try
            kill()
        Catch
        End Try
    End Sub
    Sub parse(item As GridObject)
        prelude(item.path)
        CallByName(Me, "parse_" + item.type, CallType.Method) 'input parsing
        iWb.Close(SaveChanges:=False)

        'If item.matchfile <> "" Then
        '    Dim tWb As Workbook = Xl.Workbooks.Open(item.matchfile, [ReadOnly]:=True)
        '    Dim tWs As Worksheet
        '    If tWb.Sheets.Count > 1 Then
        '        Try
        '            tWs = tWb.Sheets.Item("хранилище кодов")
        '        Catch x As Exception
        '            tWs = tWb.ActiveSheet
        '        End Try
        '    Else
        '        tWs = tWb.ActiveSheet
        '    End If



        '    Dim matcher As New MatchParser(Xl, oWs, tWs, item.type)
        '    matcher.process()
        '    succs_global_fromcodes.AddRange(matcher.succ_fromcodes)
        '    err_global.AddRange(matcher.err)
        '    tWs = Nothing
        '    tWb.Close()
        'End If
        save(item.type) ' saving input parsed
    End Sub
    Sub setTodayDate()
        _date = Date.Today
        _dateSet = True
    End Sub
    Sub parseDate(r As Integer, c As Integer)
        Dim d As String = Regex.Match(iWs.Cells(r, c).Value, "[0-9]{2}\.[0-9]{2}\.[0-9]{4}").ToString
        _date = Date.Parse(d)
        _dateSet = True
    End Sub
    Sub New(cp As String, Xl As Application)
        succs_global_fromcodes = New List(Of SuccObject)
        err_global = New List(Of String)
        Me.Xl = Xl
        cachepath = cp
        _dateSet = False




        coeffs = New SortedList(Of String, CoeffObject)
        For Each co As CoeffObject In My.Forms.Coefficients.CoeffsBindingSource
            coeffs.Add(co.type, co)
        Next


        Dim t As Dictionary(Of Object, Integer)
        rules = New Dictionary(Of String, Dictionary(Of Object, Integer))
        ' гранат
        t = New Dictionary(Of Object, Integer)
        t.Add(2, 2)
        t.Add(3, 4)
        t.Add(4, 5)
        t.Add(5, 3)
        t.Add(6, 6)
        t.Add(7, 8)
        t.Add(8, 7)
        t.Add(9, 10)
        t.Add("легковая", 16)
        t.Add(11, 9)
        t.Add("Гранат", 13)

        rules.Add("Гранат", t)
        'вакси_грузовая
        t = New Dictionary(Of Object, Integer)
        t.Add(1, 2)
        t.Add(6, 9)
        t.Add(15, 10)
        t.Add("Вакси_грузовая", 13)
        t.Add("грузовая", 16)
        rules.Add("Вакси_грузовая", t)

        'вакси_легковая
        t = New Dictionary(Of Object, Integer)
        t.Add(1, 2)
        t.Add(6, 9)
        t.Add(15, 10)
        t.Add(18, 8)
        t.Add("Вакси_легковая", 13)
        t.Add("легковая", 16)
        rules.Add("Вакси_легковая", t)

        'Вася
        t = New Dictionary(Of Object, Integer)
        t.Add(7, 2)
        t.Add(2, 3)
        t.Add(3, 5)
        t.Add(4, 6)
        t.Add(5, 8)
        t.Add(6, 10)
        t.Add("Вася", 13)
        t.Add("легковая", 16)
        rules.Add("Вася", t)

        'Кама-Кама
        t = New Dictionary(Of Object, Integer)
        t.Add(1, 2)
        t.Add(2, 3)
        t.Add(7, 10)
        t.Add("KAMA", 5)
        t.Add(8, 4)
        t.Add("Кама_Кама", 13)
        t.Add(9, 16)
        rules.Add("Кама_Кама", t)

        'Кама_Алтай
        t = New Dictionary(Of Object, Integer)
        t.Add(1, 3)
        t.Add(5, 2)
        t.Add(2, 9)
        t.Add(4, 10)
        t.Add("Алтайшина", 5)
        t.Add("Кама_Алтай", 13)
        t.Add(6, 16)
        rules.Add("Кама_Алтай", t)

        'Мотопарк
        t = New Dictionary(Of Object, Integer)
        t.Add(1, 2)
        t.Add(2, 5)
        t.Add(3, 3)
        t.Add(9, 8)
        t.Add(4, 4)
        t.Add(5, 9)
        t.Add(6, 10)
        t.Add(10, 1)
        t.Add(8, 6)
        t.Add("Мотопарк", 13)
        t.Add("легковая", 16)
        rules.Add("Мотопарк", t)

        'Олег
        t = New Dictionary(Of Object, Integer)
        t.Add(9, 2)
        t.Add(2, 3)
        t.Add(3, 5)
        t.Add(4, 6)
        t.Add(5, 8)
        t.Add(6, 10)
        t.Add(7, 9)
        t.Add(8, 1)
        t.Add("Олег", 13)
        t.Add("легковая", 16)
        rules.Add("Олег", t)

        'Опт
        t = New Dictionary(Of Object, Integer)
        t.Add(1, 1)
        t.Add(2, 3)
        t.Add(10, 2)
        t.Add(3, 5)
        t.Add(4, 9)
        t.Add(6, 10)
        t.Add(9, 8)
        t.Add("Опт", 13)
        t.Add(11, 16)
        rules.Add("Опт", t)

        'Римбат
        t = New Dictionary(Of Object, Integer)
        t.Add(1, 2)
        t.Add(2, 3)
        t.Add(3, 8)
        t.Add(4, 4)
        t.Add(5, 10)
        t.Add(7, 5)
        t.Add(8, 1)
        t.Add("Римбат", 13)
        t.Add(9, 16)
        rules.Add("Римбат", t)

        'СтартШина
        t = New Dictionary(Of Object, Integer)
        't.Add(12, 3)
        t.Add(2, 3)
        t.Add(13, 6)
        t.Add(1, 2)
        t.Add(10, 10)
        t.Add(8, 9)
        t.Add(3, 8)
        t.Add(6, 5)
        t.Add(11, 1)
        t.Add(7, 4)
        t.Add(14, 16)
        t.Add("СтартШина", 13)
        rules.Add("СтартШина", t)

        'Покрышкино
        t = New Dictionary(Of Object, Integer)
        t.Add(17, 1)
        t.Add(2, 2)
        t.Add(3, 3)
        t.Add(4, 4)
        t.Add(5, 5)
        t.Add(6, 6)
        t.Add(7, 8)
        t.Add(8, 9)
        t.Add(12, 10)
        t.Add("Покрышкино", 13)
        t.Add("легковая", 16)
        rules.Add("Покрышкино", t)

        'Диски Опт
        t = New Dictionary(Of Object, Integer)
        t.Add(1, 1)
        t.Add(3, 2)
        t.Add(4, 10)
        t.Add(6, 12)
        t.Add(10, 3)
        t.Add(11, 4)
        t.Add(12, 5)
        t.Add(13, 6)
        t.Add(14, 7)
        t.Add(15, 8)
        t.Add(16, 9)
        t.Add("Диски_Опт", 16)
        rules.Add("Диски_Опт", t)

        'Диски Покрышкино
        t = New Dictionary(Of Object, Integer)
        t.Add(21, 1)
        t.Add(19, 4)
        t.Add(20, 5)
        t.Add(3, 2)
        t.Add(4, 3)
        t.Add(6, 6)
        t.Add(7, 7)
        t.Add(9, 10)
        t.Add(13, 12)
        t.Add(17, 13)
        'DIA
        'цвет
        t.Add("Диски_Покрышкино", 16)
        rules.Add("Диски_Покрышкино", t)

        'Диски_Гранат
        t = New Dictionary(Of Object, Integer)
        t.Add(1, 2)
        t.Add(3, 3)
        t.Add(4, 4)
        t.Add(5, 5)
        t.Add(6, 6)
        t.Add(7, 7)
        t.Add(8, 8)
        t.Add(9, 9)
        t.Add(10, 10)
        t.Add(11, 11)
        t.Add(12, 12)
        t.Add(13, 13)
        t.Add(14, 1)
        t.Add("Диски_Гранат", 16)
        rules.Add("Диски_Гранат", t)
    End Sub
    Function parseCommon(type As String, Optional count As Integer = -1) As Integer
        Dim d As Dictionary(Of Object, Integer)
        d = rules.Item(type)
        Dim act
        With iWs
            If (count = -1) Then
                count = .Cells.CurrentRegion.Rows.Count
            End If
            If _dateSet = False Then
                setTodayDate()
            End If
            oWs.Range(oWs.Cells(1, 15), oWs.Cells(count, 15)).Value2 = _date.ToString("dd.MM.yyyy")
            _dateSet = False
            For Each act In d
                If TypeOf act.Key Is Integer Then ' i->j
                    .Range(.Cells(1, act.Key), .Cells(count, act.Key)).Copy()
                    oWs.Cells(1, act.Value).PasteSpecial(XlPasteType.xlPasteValues)
                ElseIf TypeOf act.Key Is Date Then

                Else ' "string"->j
                    oWs.Range(oWs.Cells(1, act.Value), oWs.Cells(count, act.Value)).Value2 = act.Key
                End If
            Next act
        End With

        'OHHH
        If Not Regex.IsMatch(type, "^Диски") Then
            placeCoeffs(type, count, 10, 11)
        Else
            Dim cv As Object = oWs.Cells(1, 13).value
            If cv Is Nothing Then
                placeCoeffs(type, count, 12, 13)
            End If

            'опт*4
            cv = oWs.Cells(1, 11).value
            If cv Is Nothing Then
                getColumn(oWs, 11, count).FormulaR1C1 = "=RC[1]*4"
            End If
        End If



        parseCommon = count
    End Function
    Sub placeCoeffs(type As String, count As Integer, get_col As Integer, place_col As Integer)



        Dim cf As CoeffObject = coeffs(type)
        With cf
            Dim i As Integer
            Dim price As Object = ExcelOps.getColumn(oWs, get_col, count, 1).Value
            Dim discount(count) As Double
            For i = 1 To count
                Dim td As Double
                If (Double.TryParse(price(i, 1), td)) Then
                    If (td <= .thr1) Then
                        td = td + .coeff
                    ElseIf td <= .thr2 Then
                        td = td * .coeff2
                    Else
                        td = td * .coeff3
                    End If
                    discount(i - 1) = td
                End If
            Next
            ExcelOps.getColumn(oWs, place_col, count, 1).Value = Xl.WorksheetFunction.Transpose(discount) ' discount
        End With
    End Sub
    Sub prelude(path As String)
        Try
            iWb = Xl.Workbooks.Open(path, ReadOnly:=True)
        Catch ex As Exception
            MsgBox("Ошибка при открытии файла " + path + ",дальнейшие действия приведут к ошибке работы.")
        End Try

        oWB = Xl.Workbooks.Add()
        Xl.Calculation = XlCalculation.xlCalculationManual
        oWs = oWB.ActiveSheet
        iWs = iWb.ActiveSheet
    End Sub
    Sub save(name As String)
        'oWs.UsedRange.ClearFormats()
        Dim temp As String = Path.GetTempFileName
        oWB.SaveAs(temp, FileFormat:=XlFileFormat.xlCSV)
        Xl.Calculation = XlCalculation.xlCalculationAutomatic
        oWB.Close()
        Try
            File.AppendAllLines(cachepath + name + ".csv", File.ReadAllLines(temp, Encoding1251), Encoding1251)
            File.Delete(temp)
        Catch ex As Exception
            MsgBox("Ошибка ввода-вывода при сохранении файла в кэш, тип " + name)
        End Try

    End Sub
    'Sub parse_Вася()
    '    iWs.Cells.UnMerge()
    '    iWs.Columns(6).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete()
    '    Dim count As Integer = iWs.Columns(1).CurrentRegion.Rows.Count

    '    ExcelOps.splitColumnInTwo(Xl, iWs, 2, "^[^ ]* ", count, 7)

    '    parseCommon("Вася", count)
    '    ExcelOps.getColumn(oWs, 1, count).FormulaR1C1 = "=RC3"

    'End Sub
    'Sub parse_Вакси_грузовая()

    '    Dim head_del As Integer = iWs.Cells.Find(What:="Размер", _
    '               SearchOrder:=XlSearchOrder.xlByRows, _
    '               SearchDirection:=XlSearchDirection.xlNext).Row _
    '               + 1 ' merged cells
    '    iWs.Rows("1:" + head_del.ToString).Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)

    '    iWs.Cells.UnMerge()

    '    iWs.Columns(3).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete()
    '    iWs.Columns(6).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete()
    '    Dim rownumber As Integer = iWs.Cells(1, 2).currentregion.rows.count
    '    Dim src As Object = ExcelOps.getColumn(iWs, 3, rownumber).Value
    '    Dim model(rownumber) As String
    '    Dim vendor(rownumber) As String
    '    Dim code(rownumber) As String
    '    For i As Integer = 1 To rownumber
    '        Dim t As String = src(i, 1)
    '        code(i - 1) = Regex.Match(t, "^[^)]*").Value
    '        t = Regex.Replace(t, "^[^A-Za-z]*", "") 'kill everything before vendorname
    '        vendor(i - 1) = Regex.Match(t, "^[A-Za-z]+").ToString ' vendorname
    '        model(i - 1) = Regex.Replace(t, "^[A-Za-z]+ *", "") ' everything after vendorname
    '    Next
    '    ExcelOps.getColumn(oWs, 3, rownumber).Value = Xl.WorksheetFunction.Transpose(model)
    '    ExcelOps.getColumn(oWs, 5, rownumber).Value = Xl.WorksheetFunction.Transpose(vendor)
    '    ExcelOps.getColumn(oWs, 1, rownumber).Value = Xl.WorksheetFunction.Transpose(code)
    '    parseCommon("Вакси_грузовая", rownumber)
    'End Sub
    'Sub parse_Вакси_легковая()
    '    'ваксигрузокопипаста
    '    Dim head_del As Integer = iWs.Cells.Find(What:="Размер", _
    '               SearchOrder:=XlSearchOrder.xlByRows, _
    '               SearchDirection:=XlSearchDirection.xlNext).Row _
    '               + 1 ' merged cells
    '    'Dim headrow As Range = iWs.Rows(head_del)
    '    iWs.Rows("1:" + head_del.ToString).Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
    '    'Dim col_remains As Integer = headrow.Find(What:="Минск", SearchOrder:=XlSearchOrder.xlByColumns, _
    '    '            SearchDirection:=XlSearchDirection.xlNext).Column ' остатки минские

    '    'Dim col_price_s As Range = headrow.Find(What:="Цена в", SearchOrder:=XlSearchOrder.xlByColumns, _
    '    '            SearchDirection:=XlSearchDirection.xlNext) ' минская цена
    '    'Dim col_price As Integer = headrow.FindNext(col_price_s).Column
    '    iWs.Cells.UnMerge()
    '    Dim rownumber As Integer = iWs.Cells.CurrentRegion.Rows.Count

    '    'a little bit new
    '    fillFromHorizontal(3, 18, rownumber)
    '    'end

    '    iWs.Columns(6).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete()
    '    rownumber = iWs.Cells(1, 1).currentregion.rows.count
    '    Dim src As Object = ExcelOps.getColumn(iWs, 3, rownumber).Value
    '    Dim model(rownumber) As String
    '    Dim vendor(rownumber) As String
    '    Dim code(rownumber) As String
    '    'ваксигрузокопипаста кончилась
    '    For i As Integer = 1 To rownumber
    '        Dim t As String = src(i, 1)
    '        'here goes dict
    '        For Each d As DictObject In My.Forms.Dictionary.dict
    '            If (Regex.IsMatch(t, d.source)) Then
    '                t = Regex.Replace(t, d.source, d.replacement)
    '                Exit For
    '            End If
    '        Next
    '        'then as usual
    '        code(i - 1) = Regex.Match(t, "^[^)]*").Value
    '        t = Regex.Replace(t, "^[^)]*\)? *", "")
    '        t = Regex.Replace(t, "^ ?[AА] ?/? ?[Шш]ин[аы]", "") ' AA - first latin,then cyrillic
    '        t = Regex.Replace(t, "^\W*", "") 'kill everything before vendorname
    '        vendor(i - 1) = Regex.Match(t, "^[\w-_]+").ToString ' vendorname
    '        model(i - 1) = Trim(Regex.Replace(t, "^[\w-_]+ *", "")) ' everything after vendorname
    '    Next
    '    ExcelOps.getColumn(oWs, 3, rownumber).Value = Xl.WorksheetFunction.Transpose(model)
    '    ExcelOps.getColumn(oWs, 5, rownumber).Value = Xl.WorksheetFunction.Transpose(vendor)
    '    ExcelOps.getColumn(oWs, 1, rownumber).Value = Xl.WorksheetFunction.Transpose(code)
    '    parseCommon("Вакси_легковая", rownumber)


    'End Sub
    'Sub parse_Гранат()
    '    iWs = iWb.Sheets("2. Шины Лето-Зима")

    '    iWs.Rows("1:1").Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
    '    Dim count As Integer = parseCommon("Гранат")
    '    ExcelOps.getColumn(oWs, 1, count).FormulaR1C1 = "=CONCATENATE(RC2,RC4,RC5,RC3)"

    'End Sub
    'Sub parse_Кама_Алтай()
    '    Dim i As Integer
    '    iWs.Rows(1).delete(Shift:=XlDeleteShiftDirection.xlShiftUp)

    '    Dim count As Integer = iWs.Cells.CurrentRegion.Rows.Count
    '    fillFromHorizontal(3, 6, count)

    '    Dim other As Integer = iWs.Columns.Find("Другое", SearchOrder:=XlSearchOrder.xlByRows).Row
    '    ExcelOps.getColumn(iWs, 1, count, other).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp)


    '    iWs.Columns(3).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
    '    count = iWs.Cells.CurrentRegion.Rows.Count
    '    Dim srcmodel As Object = ExcelOps.getColumn(iWs, 1, count).Value
    '    Dim tire_src As Object = ExcelOps.getColumn(iWs, 6, count).Value
    '    Dim sizes(count) As String
    '    Dim actual_model(count) As String
    '    Dim tire_res(count) As String
    '    For i = 1 To count - 1
    '        Dim t As String = srcmodel(i, 1)
    '        t = Regex.Replace(t, "/ *", "/")
    '        sizes(i - 1) = Trim(Regex.Match(t, "^[^ ]* ").Value)
    '        actual_model(i - 1) = Trim(Regex.Replace(t, "^[^ ]* ", ""))

    '        t = tire_src(i, 1)
    '        Select Case t
    '            Case "Грузовые", "Сельскохозяйственные, погрузчики"
    '                tire_res(i - 1) = "грузовая"
    '            Case Else
    '                tire_res(i - 1) = "легковая"
    '        End Select
    '    Next
    '    ExcelOps.getColumn(iWs, 5, count).Value = Xl.WorksheetFunction.Transpose(sizes)
    '    ExcelOps.getColumn(iWs, 1, count).Value = Xl.WorksheetFunction.Transpose(actual_model)
    '    ExcelOps.getColumn(iWs, 6, count).Value = Xl.WorksheetFunction.Transpose(tire_res)
    '    ExcelOps.getColumn(iWs, 4, count).FormulaR1C1 = "=RC3/" + BYR_USD_CONV.ToString
    '    parseCommon("Кама_Алтай", count)
    '    ExcelOps.getColumn(oWs, 1, count).FormulaR1C1 = "=CONCATENATE(RC2,RC3)"

    'End Sub
    'Sub parse_Кама_Кама()

    '    parseDate(1, 2)

    '    iWs.Cells.UnMerge()
    '    iWs.Rows("1:3").delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
    '    iWs.Columns(1).delete(Shift:=XlDeleteShiftDirection.xlShiftToLeft)


    '    Dim count As Integer = iWs.Cells(1, 1).CurrentRegion.Rows.Count
    '    fillFromHorizontal(5, 9, count)
    '    Dim tire_orig As Object = ExcelOps.getColumn(iWs, 9, count).Value
    '    Dim tire_res(count) As String
    '    For i As Integer = 1 To count
    '        Dim t As String = tire_orig(i, 1)
    '        Select Case t
    '            Case "Шины для грузовых автомобилей", "Шины сельскохозяйственные", "Шины для погрузчиков", "Шины цельнометаллокордные KAMA"
    '                tire_res(i - 1) = "грузовая"
    '            Case Else
    '                tire_res(i - 1) = "легковая"
    '        End Select
    '    Next
    '    ExcelOps.getColumn(iWs, 9, count).Value = Xl.WorksheetFunction.Transpose(tire_res)


    '    iWs.Columns(5).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
    '    count = iWs.Cells(1, 1).CurrentRegion.Rows.Count
    '    iWs.Columns(7).formulaR1C1 = "=RC5/" + BYR_USD_CONV.ToString
    '    iWs.Columns(8).formulaR1C1 = "=CONCATENATE(RC3,RC4)"
    '    parseCommon("Кама_Кама", count)
    '    ExcelOps.getColumn(oWs, 1, count).FormulaR1C1 = "=CONCATENATE(RC2,RC3,RC4)"

    'End Sub
    'Sub parse_Мотопарк()

    '    Dim tWb As Workbook = Xl.Workbooks.Add
    '    Dim tws As Worksheet = tWb.ActiveSheet
    '    Dim curline As Integer = 1
    '    For Each ws As Worksheet In iWb.Worksheets
    '        'удадение
    '        'mWs.Rows("1:" + rowsdel.ToString).Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)

    '        ws.Columns(7).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
    '        Try
    '            Dim headers As Range = ws.Columns(1).Find("Размер", SearchOrder:=XlSearchOrder.xlByRows)
    '            Try
    '                headers.Find("Опт 1", SearchOrder:=XlSearchOrder.xlByColumns).EntireColumn.Delete(Shift:=XlDeleteShiftDirection.xlShiftToLeft)
    '            Catch
    '            End Try
    '            headers.EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
    '            ws.Columns(1).Find("Размер", SearchOrder:=XlSearchOrder.xlByRows).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
    '        Catch x As NullReferenceException
    '        End Try
    '        ws.Columns(1).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
    '        Dim count As Integer = ws.Columns(1).CurrentRegion.Rows.Count
    '        ExcelOps.getColumn(ws, 9, count).Value = ws.Name
    '        ws.Cells.CurrentRegion.Copy()
    '        tws.Cells(curline, 1).PasteSpecial(XlPasteType.xlPasteValues)
    '        curline = curline + count
    '    Next
    '    iWs = Nothing
    '    iWb.Close()
    '    iWb = tWb
    '    iWs = tws
    '    tws = Nothing
    '    tWb = Nothing
    '    ExcelOps.getColumn(iWs, 10, curline - 1).FormulaR1C1 = "=CONCATENATE(RC1,RC3,RC4)"
    '    parseCommon("Мотопарк", curline - 1)

    'End Sub
    'Sub parse_Олег()

    '    iWs.Cells.UnMerge()
    '    iWs.Columns(6).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
    '    Dim count As Integer = iWs.Columns(1).CurrentRegion.rows.Count
    '    Dim src As Object = ExcelOps.getColumn(iWs, 5, count).Value ' в столбце с сезоном мелькают надписи вида "1шт", достанем
    '    Dim season_replaced(count) As String
    '    Dim size(count) As String
    '    Dim tirecount(count) As Object ' not integer, because if not set defaults to 0

    '    For i As Integer = 1 To count
    '        Dim t As String = src(i, 1)
    '        Dim m As Match = Regex.Match(t, "[0-9]+")
    '        If (m.Success) Then
    '            tirecount(i - 1) = Convert.ToInt32(m.Value)
    '        End If
    '        season_replaced(i - 1) = Regex.Replace(t, " +[0-9]+ ?шт", "")
    '    Next


    '    ExcelOps.splitColumnInTwo(Xl, iWs, 2, "^[^ ]* ", count, 9)

    '    ExcelOps.getColumn(iWs, 5, count).Value = Xl.WorksheetFunction.Transpose(season_replaced)
    '    ExcelOps.getColumn(iWs, 7, count).Value = Xl.WorksheetFunction.Transpose(tirecount)

    '    ExcelOps.getColumn(iWs, 8, count).FormulaR1C1 = "=RC2"
    '    parseCommon("Олег", count)

    'End Sub
    Sub parse_Опт()


        iWs.Rows("1:5").Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
        Dim count As Integer = iWs.Columns(1).CurrentRegion.rows.Count
        Dim src As Object = ExcelOps.getColumn(iWs, 9, count).Value ' в столбце с сезоном есть "диски, передние, задние, прицепные", нас интересует только сезон, очищаем неугодные
        Dim res(count) As String
        Dim tiretype(count) As String
        For i As Integer = 1 To count
            Dim t As String = LCase(src(i, 1))
            If t = "передние" Or t = "задние" Or t = "прицепные" Then
                tiretype(i - 1) = "грузовая"
            Else
                tiretype(i - 1) = "легковая"
            End If
            Select Case t
                Case "зима", "лето", "всесезонные", "передние", "задние", "прицепные"
                    res(i - 1) = t
                Case "диски"
                    res(i - 1) = ""
                Case Else
                    res(i - 1) = "-"
            End Select
        Next
        ExcelOps.getColumn(iWs, 9, count).Value = Xl.WorksheetFunction.Transpose(res)
        ExcelOps.getColumn(iWs, 11, count).Value = Xl.WorksheetFunction.Transpose(tiretype)
        ExcelOps.getColumn(iWs, 9, count).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
        count = iWs.Columns(1).CurrentRegion.rows.Count

        ExcelOps.splitColumnInTwo(Xl, iWs, 2, "^[^ ]* ", count, 10, 2)

        parseCommon("Опт", count)

    End Sub
    'Sub parse_Покрышкино()

    '    iWs.Cells.UnMerge()
    '    Dim c As Range = iWs.Columns(2).Find("Типоразмер", SearchOrder:=XlSearchOrder.xlByRows)
    '    iWs.Rows("1:" + (c.Row + 2).ToString).delete()
    '    iWs.Columns(3).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp)
    '    Dim count As Integer = iWs.Cells(1, 1).currentregion.rows.count
    '    ExcelOps.getColumn(iWs, 3, count).Replace(What:="""АКЦИЯ""", Replacement:="", LookAt:=XlLookAt.xlPart, MatchCase:=False)
    '    'ExcelOps.getColumn(iWs, 18, count).FormulaR1C1 = "=RC16/" + BYR_USD_CONV.ToString
    '    ExcelOps.getColumn(iWs, 17, count).FormulaR1C1 = "=CONCATENATE(RC2,RC3,RC4,RC5)"
    '    parseCommon("Покрышкино")

    'End Sub
    'Sub parse_Римбат()
    '    iWs = iWb.Sheets("Шины")
    '    Dim currency_rimbat As Double = iWs.Cells(6, 5).Value()
    '    Dim d As Range = iWs.Cells.Find("размер", SearchOrder:=XlSearchOrder.xlByRows)

    '    parseDate(1, 1)
    '    iWs.Rows("1:" + d.Row.ToString).Delete()
    '    Dim count As Integer = iWs.Cells.CurrentRegion.Rows.Count

    '    iWs.Cells.UnMerge()
    '    fillFromHorizontal(6, 7, count)
    '    fillFromHorizontal(6, 9, count, 2)

    '    Dim tire_orig As Object = ExcelOps.getColumn(iWs, 9, count).Value
    '    Dim tire_res(count) As String
    '    For i As Integer = 1 To count
    '        Dim t As String = tire_orig(i, 1)
    '        Select Case t
    '            Case "Грузовые шины", "Сельскохозяйсвенные шины"
    '                tire_res(i - 1) = "грузовая"
    '            Case Else
    '                tire_res(i - 1) = "легковая"
    '        End Select
    '    Next
    '    ExcelOps.getColumn(iWs, 9, count).Value = Xl.WorksheetFunction.Transpose(tire_res)



    '    ExcelOps.getColumn(iWs, 6, count).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
    '    count = iWs.Cells.CurrentRegion.Rows.Count 'recount rows
    '    ExcelOps.getColumn(iWs, 5, count).FormulaR1C1 = "=RC6/" + currency_rimbat.ToString
    '    ExcelOps.getColumn(iWs, 8, count).FormulaR1C1 = "=CONCATENATE(RC1,RC2)"
    '    parseCommon("Римбат", count)

    'End Sub
    'Sub parse_СтартШина()
    '    'MsgBox("Обработка прайса 'СтартШина' недоработана")
    '    iWs.Rows("1:8").Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)

    '    Dim count As Integer
    '    iWs.Cells.UnMerge()

    '    Dim cameras As Range = iWs.Columns(1).Find("Камеры", SearchOrder:=XlSearchOrder.xlByRows)
    '    count = cameras.CurrentRegion.Rows.Count
    '    ExcelOps.getColumn(iWs, 1, count, cameras.Row).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
    '    count = iWs.Cells(1, 1).CurrentRegion.Rows.Count
    '    fillFromHorizontal(7, 14, count)

    '    Dim tire_orig As Object = ExcelOps.getColumn(iWs, 14, count).Value
    '    Dim tire_res(count) As String
    '    For i As Integer = 1 To count
    '        Dim t As String = tire_orig(i, 1)
    '        Select Case UCase(Trim(t))
    '            Case "ШИНЫ ДЛЯ ТРАКТОРОВ И С/Х МАШИН", "ШИНЫ ДЛЯ ПОГРУЗЧИКОВ", "ШИНЫ ДЛЯ ГРУЗОВЫХ АВТОМОБИЛЕЙ И СПЕЦТЕХНИКИ"
    '                tire_res(i - 1) = "грузовая"
    '            Case Else
    '                tire_res(i - 1) = "легковая"
    '        End Select
    '    Next
    '    ExcelOps.getColumn(iWs, 14, count).Value = Xl.WorksheetFunction.Transpose(tire_res)


    '    iWs.Columns(9).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
    '    count = iWs.Columns(9).currentregion.rows.count



    '    ExcelOps.getColumn(iWs, 10, count).FormulaR1C1 = "=RC9/" + BYR_USD_CONV.ToString
    '    ExcelOps.getColumn(iWs, 11, count).FormulaR1C1 = "=CONCATENATE(RC1,RC2,RC4)"
    '    'ExcelOps.getColumn(iWs, 12, count).FormulaR1C1 = "=CONCATENATE(RC2,RC4,RC5)"
    '    ExcelOps.getColumn(iWs, 13, count).FormulaR1C1 = "=CONCATENATE(RC4,RC5)"

    '    parseCommon("СтартШина", count)

    'End Sub
    'Sub parse_Диски_Опт()
    '    parseDate(2, 2)
    '    iWs.Rows("1:5").Delete(XlDeleteShiftDirection.xlShiftUp)
    '    Dim count As Integer = iWs.Cells(1, 1).CurrentRegion.Rows.count
    '    Dim orig_season As Object = getColumn(iWs, 9, count).Value
    '    Dim disc(count) As String
    '    For i As Integer = 1 To count
    '        If LCase(orig_season(i, 1)) <> "диски" Then
    '            disc(i - 1) = Nothing
    '        Else
    '            disc(i - 1) = orig_season(i, 1)
    '        End If
    '    Next

    '    getColumn(iWs, 9, count).Value = Xl.WorksheetFunction.Transpose(disc)
    '    getColumn(iWs, 9, count).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp)
    '    count = iWs.Cells(1, 1).CurrentRegion.Rows.count

    '    Dim src As Object = getColumn(iWs, 2, count).Value
    '    Dim model(count) As String
    '    Dim j_(count) As String
    '    Dim diameter(count) As String
    '    Dim PCD(count) As String
    '    Dim ET(count) As String
    '    Dim DIA(count) As String
    '    Dim color(count) As String
    '    For i As Integer = 1 To count
    '        Dim t As String = src(i, 1)
    '        Try
    '            Dim spl() As String = Regex.Split(t, " +")
    '            model(i - 1) = spl(0)

    '            spl(1) = Regex.Replace(spl(1), "х", "x") 'yeah

    '            Dim tempspl() As String = Regex.Split(spl(1), "x")
    '            j_(i - 1) = tempspl(0)
    '            diameter(i - 1) = tempspl(1)
    '            PCD(i - 1) = spl(2)
    '            ET(i - 1) = spl(3)
    '            DIA(i - 1) = spl(4)

    '            color(i - 1) = ""
    '            For j As Integer = 5 To spl.Length - 1
    '                color(i - 1) = color(i - 1) + " " + spl(j)
    '            Next
    '        Catch x As IndexOutOfRangeException
    '            model(i - 1) = t
    '            j_(i - 1) = ""
    '            diameter(i - 1) = ""
    '            PCD(i - 1) = ""
    '            ET(i - 1) = ""
    '            DIA(i - 1) = ""
    '            color(i - 1) = ""
    '        End Try
    '    Next

    '    getColumn(iWs, 10, count).Value = Xl.WorksheetFunction.Transpose(model)
    '    getColumn(iWs, 11, count).Value = Xl.WorksheetFunction.Transpose(diameter)
    '    getColumn(iWs, 12, count).Value = Xl.WorksheetFunction.Transpose(j_)
    '    getColumn(iWs, 13, count).Value = Xl.WorksheetFunction.Transpose(PCD)
    '    getColumn(iWs, 14, count).Value = Xl.WorksheetFunction.Transpose(ET)
    '    getColumn(iWs, 15, count).Value = Xl.WorksheetFunction.Transpose(DIA)
    '    getColumn(iWs, 16, count).Value = Xl.WorksheetFunction.Transpose(color)

    '    parseCommon("Диски_Опт")
    '    'F  7x16 5x108 ET43 D70.1 HIGH GLOSS,, где
    '    'F -модель
    '    '7 - J"
    '    '16 - диаметр
    '    '5x108(-PCD, мм)
    '    'ET43(-ET, мм)
    '    'D70(0.1 - DIA, мм)
    '    'HIGH GLOSS - цвет
    'End Sub
    'Sub parse_Диски_Гранат()
    '    iWs = iWb.Sheets("1. Диски Литые")
    '    iWs.Rows("1:2").Delete(XlDeleteShiftDirection.xlShiftUp)
    '    getColumn(iWs, 14).FormulaR1C1 = "=CONCATENATE(RC1,RC2,RC3,RC4,RC5,RC6,RC7,RC8)"
    '    parseCommon("Диски_Гранат")
    'End Sub
    'Sub parse_Диски_Покрышкино()
    '    'imported from Покрышкино
    '    iWs.Cells.UnMerge()
    '    Dim c As Range = iWs.Columns(2).Find("Типоразмер", SearchOrder:=XlSearchOrder.xlByRows)
    '    iWs.Rows("1:" + (c.Row + 2).ToString).delete()

    '    Dim count As Integer = iWs.Cells(1, 2).CurrentRegion.Rows.count
    '    'fillFromHorizontal(3, 14, count, 5)
    '    iWs.Columns(1).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp)
    '    count = iWs.Cells(1, 1).CurrentRegion.Rows.count
    '    Dim src As Object = getColumn(iWs, 2, count).Value 'типоразмер
    '    Dim j_(count) As String
    '    Dim diameter(count) As String
    '    For i As Integer = 1 To count
    '        src(i, 1) = Regex.Replace(src(i, 1), "х", "x")
    '        Dim spl() As String = Regex.Split(src(i, 1), "x")
    '        If spl.Length > 1 Then
    '            j_(i - 1) = spl(0)
    '            diameter(i - 1) = spl(1)
    '        Else
    '            diameter(i - 1) = spl(0)
    '            j_(i - 1) = ""
    '        End If
    '    Next

    '    getColumn(iWs, 19, count).Value = Xl.WorksheetFunction.Transpose(diameter)
    '    getColumn(iWs, 20, count).Value = Xl.WorksheetFunction.Transpose(j_)

    '    getColumn(iWs, 21).FormulaR1C1 = "=CONCATENATE(RC2,RC4,RC5,RC6,RC7)"
    '    parseCommon("Диски_Покрышкино")
    'End Sub
    Sub kill() 'As List(Of SuccObject) ' there are some IDisposable and such
        oWB = Nothing
        iWb = Nothing
        'Xl.Quit()
        Xl = Nothing
        ' kill = succs_global
    End Sub
    Sub fillFromHorizontal(empty_col As Integer, col_to_set As Integer, count As Integer, Optional meaning_col As Integer = 1)
        Dim horizontal As New List(Of Integer)
        Dim t As Range = iWs.Columns(empty_col).Find("", SearchOrder:=XlSearchOrder.xlByRows)
        horizontal.Add(1)
        While t.Row < count
            horizontal.Add(t.Row)
            t = iWs.Columns(empty_col).FindNext(t)
        End While
        'iWs.Columns(7).Clear()
        For i As Integer = 0 To horizontal.Count - 2 ' setting vendors
            iWs.Range(iWs.Cells(horizontal(i), col_to_set), iWs.Cells(horizontal(i + 1) - 1, col_to_set)).Value = iWs.Cells(horizontal(i), meaning_col).Value
        Next
        iWs.Range(iWs.Cells(horizontal(horizontal.Count - 1), col_to_set), iWs.Cells(count, col_to_set)).Value = iWs.Cells(horizontal(horizontal.Count - 1), meaning_col).Value
    End Sub


End Class
